﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HtmlAgilityPack;
using System.Net;
using System.IO;
using System.Web;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;
using System.Text.RegularExpressions;

namespace LenderClearingHouse
{
    public static class PageScrapes
    {
       /*
        * This Section Does the Parcell scrape to add additional
        * details to the master list pulled from Nevada Legal News
       */
      public static DataTable scrapeParcell(string page, string apn, bool convertLandUseToCode = false)
      {
        DataTable table = ImportDataBuilders.ParcellAppraisal();
        DataRow row = table.NewRow();
        bool hasValues = false;
        row["APN"] = apn;

        HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
        doc.Load(new StringReader(page));

        HtmlNode node = doc.GetElementbyId("lblStories");
        if (node != null)
        {
          row["Stories"] = node.InnerText;
          hasValues = true;
        }

        node = doc.GetElementbyId("lblConstrYr");
        if (node != null)
        {
          row["ConstructYear"] = node.InnerText;
          hasValues = true;
        }

        node = doc.GetElementbyId("lblAcres");
        if (node != null)
        {
          row["LotSize"] = node.InnerText.Replace("Acres", "").Trim();
          hasValues = true;
        }

        node = doc.GetElementbyId("lblBedrooms");
        if (node != null)
        {
          row["Bedrooms"] = node.InnerText;
          hasValues = true;
        }

        node = doc.GetElementbyId("lblBath");
        if (node != null)
        {
          row["Bathrooms"] = node.InnerText;
          hasValues = true;
        }

        node = doc.GetElementbyId("lblPool");
        if (node != null)
        {
          row["Pool"] = node.InnerText;
          hasValues = true;
        }

        node = doc.GetElementbyId("lblSpa");
        if (node != null)
        {
          row["Spa"] = node.InnerText;
          hasValues = true;
        }

        node = doc.GetElementbyId("lblLandUse");
        if (node != null)
        {
            /*
             * If the scrape can’t figure it out, then it needs to default to OTH for other.
             * Also, remember that the cells must have override capacity in case it is wrong so I can fix it.
             * Also, understand that there may be multiple types of descriptions on the left,
             * but the scrape must be looking for ‘single family’ for instance regardless of what is in front
             * of it in the description.  Likewise for the others as well.  If the scrape reads the key word – single family,
             * fourplex, apartments, townhouses, condominium, commercial, duplex, highrise – then it must
             * recognize it and put in the appropriate 3 letter type code.
             */
            string code = "OTH";
            string innerText = node.InnerText.ToLower();

            if (!convertLandUseToCode)
            {
                code = innerText;
            }
            else if (innerText.Contains("family"))
            {
                code = "SFR";
            }
            else if (innerText.Contains("fourplex"))
            {
                code = "4Px";
            }
            else if (innerText.Contains("apartments"))
            {
                code = "APT";
            }
            else if (innerText.Contains("townhouses"))
            {
                code = "TWN";
            }
            else if (innerText.Contains("condominium"))
            {
                code = "CON";
            }
            else if (innerText.Contains("commercial"))
            {
                code = "CMM";
            }
            else if (innerText.Contains("duplex"))
            {
                code = "DUP";
            }
            else if (innerText.Contains("highrise"))
            {
                code = "HIR";
            }


            row["LandUse"] = code;
          hasValues = true;
        }

        decimal totalSqft = 0;

        node = doc.GetElementbyId("lblFirstFloor");
        if (node != null)
        {
          row["FirstFloorSqft"] = node.InnerText;
          hasValues = true;
          
          totalSqft += decimal.Parse(node.InnerText);
        }

        node = doc.GetElementbyId("lblSecondFloor");
        if (node != null)
        {
          row["SecondFloorSqft"] = node.InnerText;
          hasValues = true;

          totalSqft += decimal.Parse(node.InnerText);
        }

        node = doc.GetElementbyId("lblBasement");
        if (node != null)
        {
            totalSqft += decimal.Parse(node.InnerText);
        }

        if (totalSqft > 0)
        {
            row["TotalSqft"] = totalSqft;
            hasValues = true;
        }

        node = doc.GetElementbyId("lblGarage");
        if (node != null)
        {
          row["GarageSqft"] = node.InnerText;
          hasValues = true;
        }

        node = doc.GetElementbyId("lblDesc1");
        if (node != null)
        {
          hasValues = true;
          string assessorDescr = node.InnerText;

          int i = 2;
          while (true)
          {
            node = doc.GetElementbyId("lblDesc" + i.ToString());
            i++;
            if (node == null) break;

            if(node.InnerText.Trim() == "") continue;

            assessorDescr += "<br/>" + node.InnerText;            
          }

          row["AssessorDesc"] = assessorDescr;
        }

        if (hasValues) table.Rows.Add(row);

        return table;
      }

        /*
        //Scrapes the parcell page
        public static DataTable scrapeParcell(string page, string apn)
        {
          return scrapeParcell(page, apn);

            DataTable table = ImportDataBuilders.ParcellAppraisal();
            string output = String.Empty;
            int evaluate;
            string[] columnValue = new string[14];
            int controlValue = 1;
            int recordCount = 0;
            int index = 0; //used to trim

            HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
            doc.Load(new StringReader(page));
            try
            {
                evaluate = doc.DocumentNode.SelectNodes("//table//tr//td[@class='transactionTableBodyText']").Count; //evaluates amount of returned rows
                //Does the loop for rows with appraisal record
                if (evaluate == 54)
                {
                    foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr//td[@class='transactionTableBodyText']"))
                    {
                        if (controlValue.Equals(50)) //write to table
                        {
                            columnValue[recordCount] = cell.InnerText.Trim();
                            table = ImportDataBuilders.ParcellAppraisalRow(table, columnValue);
                        }
                        else if (controlValue.Equals(1) || controlValue.Equals(4) || controlValue.Equals(33) || controlValue.Equals(34) || controlValue.Equals(36) || controlValue.Equals(38) || controlValue.Equals(41) || controlValue.Equals(42)
                            || controlValue.Equals(43) || controlValue.Equals(44) || controlValue.Equals(45) || controlValue.Equals(46) || controlValue.Equals(48))
                        {
                            if (controlValue.Equals(4))
                            {
                                output = cell.InnerText.Trim();
                                index = output.IndexOf("\r"); output = output.Substring(0, index); //Remove everything after \r
                                columnValue[recordCount] = output;
                            }
                            else { columnValue[recordCount] = cell.InnerText.Trim(); }
                            recordCount++;
                        }
                        controlValue++;
                    }
                }
                else if (evaluate == 55) //Does the loop for rows with appraisal record with comments
                {
                    foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr//td[@class='transactionTableBodyText']"))
                    {
                        if (controlValue.Equals(51)) //write to table
                        {
                            columnValue[recordCount] = cell.InnerText.Trim();
                            table = ImportDataBuilders.ParcellAppraisalRow(table, columnValue);
                        }
                        else if (controlValue.Equals(1) || controlValue.Equals(4) || controlValue.Equals(34) || controlValue.Equals(35) || controlValue.Equals(37) || controlValue.Equals(39) || controlValue.Equals(42) || controlValue.Equals(43)
                            || controlValue.Equals(44) || controlValue.Equals(45) || controlValue.Equals(46) || controlValue.Equals(47) || controlValue.Equals(49))
                        {
                            if (controlValue.Equals(4))
                            {
                                output = cell.InnerText.Trim();
                                index = output.IndexOf("\r"); output = output.Substring(0, index); //Remove everything after \r
                                columnValue[recordCount] = output;
                            }
                            else { columnValue[recordCount] = cell.InnerText.Trim(); }
                            recordCount++;
                        }
                        controlValue++;
                    }
                }
                else if (evaluate == 39) //Does the loop for rows without appraisal record
                {
                    foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr//td[@class='transactionTableBodyText']"))
                    {
                        if (controlValue.Equals(39)) //write to table
                        {
                            columnValue[3] = ""; columnValue[4] = ""; columnValue[5] = ""; columnValue[6] = ""; columnValue[7] = ""; //add blank missing values
                            columnValue[8] = ""; columnValue[9] = ""; columnValue[10] = ""; columnValue[11] = ""; columnValue[12] = ""; columnValue[13] = "";
                            table = ImportDataBuilders.ParcellAppraisalRow(table, columnValue);
                        }
                        else if (controlValue.Equals(1) || controlValue.Equals(4) || controlValue.Equals(33) || controlValue.Equals(34))
                        {
                            if (controlValue.Equals(4))
                            {
                                output = cell.InnerText.Trim();
                                index = output.IndexOf("\r"); output = output.Substring(0, index); //Remove everything after \r
                                columnValue[recordCount] = output;
                            }
                            else { columnValue[recordCount] = cell.InnerText.Trim(); }
                            recordCount++;
                        }
                        controlValue++;
                    }
                }
                else if (evaluate == 40) //Does the loop for rows without appraisal record and comments
                {
                    foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr//td[@class='transactionTableBodyText']"))
                    {
                        if (controlValue.Equals(39)) //write to table
                        {
                            columnValue[3] = ""; columnValue[4] = ""; columnValue[5] = ""; columnValue[6] = ""; columnValue[7] = ""; //add blank missing values
                            columnValue[8] = ""; columnValue[9] = ""; columnValue[10] = ""; columnValue[11] = ""; columnValue[12] = ""; columnValue[13] = "";
                            table = ImportDataBuilders.ParcellAppraisalRow(table, columnValue);
                        }
                        else if (controlValue.Equals(1) || controlValue.Equals(4) || controlValue.Equals(34) || controlValue.Equals(35))
                        {
                            if (controlValue.Equals(4))
                            {
                                output = cell.InnerText.Trim();
                                index = output.IndexOf("\r"); output = output.Substring(0, index); //Remove everything after \r
                                columnValue[recordCount] = output;
                            }
                            else { columnValue[recordCount] = cell.InnerText.Trim(); }
                            recordCount++;
                        }
                        controlValue++;
                    }
                }
            }
            catch (Exception)
            {
                columnValue[0] = ""; columnValue[1] = ""; columnValue[2] = ""; columnValue[3] = ""; columnValue[4] = ""; columnValue[5] = ""; columnValue[6] = ""; columnValue[7] = ""; //add blank missing values
                columnValue[8] = ""; columnValue[9] = ""; columnValue[10] = ""; columnValue[11] = ""; columnValue[12] = ""; columnValue[13] = "";
                table = ImportDataBuilders.ParcellAppraisalRow(table, columnValue);
            }
            
            return table;
        }*/

        //Scrapes the parcell page
        public static DataTable LVREOscrapeParcell(string page)
        {
            DataTable table = ImportDataBuilders.ParcellAppraisal();
            string output = String.Empty;
            int evaluate;
            string[] columnValue = new string[14];
            int controlValue = 1;
            int recordCount = 0;
            int index = 0; //used to trim

            HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
            doc.Load(new StringReader(page));
            try
            {
                evaluate = doc.DocumentNode.SelectNodes("//table//tr//td[@class='transactionTableBodyText']").Count; //evaluates amount of returned rows
                //Does the loop for rows with appraisal record
                if (evaluate == 54)
                {
                    foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr//td[@class='transactionTableBodyText']"))
                    {
                        if (controlValue.Equals(50)) //write to table
                        {
                            columnValue[recordCount] = cell.InnerText.Trim();
                            table = ImportDataBuilders.ParcellAppraisalRow(table, columnValue);
                        }
                        else if (controlValue.Equals(1) || controlValue.Equals(4) || controlValue.Equals(33) || controlValue.Equals(34) || controlValue.Equals(36) || controlValue.Equals(38) || controlValue.Equals(41) || controlValue.Equals(42)
                            || controlValue.Equals(43) || controlValue.Equals(44) || controlValue.Equals(45) || controlValue.Equals(46) || controlValue.Equals(48))
                        {
                            if (controlValue.Equals(4))
                            {
                                output = cell.InnerText.Trim();
                                index = output.IndexOf("\r"); output = output.Substring(0, index); //Remove everything after \r
                                columnValue[recordCount] = output;
                            }
                            else { columnValue[recordCount] = cell.InnerText.Trim(); }
                            recordCount++;
                        }
                        controlValue++;
                    }
                }
                else if (evaluate == 55) //Does the loop for rows with appraisal record with comments
                {
                    foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr//td[@class='transactionTableBodyText']"))
                    {
                        if (controlValue.Equals(51)) //write to table
                        {
                            columnValue[recordCount] = cell.InnerText.Trim();
                            table = ImportDataBuilders.ParcellAppraisalRow(table, columnValue);
                        }
                        else if (controlValue.Equals(1) || controlValue.Equals(4) || controlValue.Equals(34) || controlValue.Equals(35) || controlValue.Equals(37) || controlValue.Equals(39) || controlValue.Equals(42) || controlValue.Equals(43)
                            || controlValue.Equals(44) || controlValue.Equals(45) || controlValue.Equals(46) || controlValue.Equals(47) || controlValue.Equals(49))
                        {
                            if (controlValue.Equals(4))
                            {
                                output = cell.InnerText.Trim();
                                index = output.IndexOf("\r"); output = output.Substring(0, index); //Remove everything after \r
                                columnValue[recordCount] = output;
                            }
                            else { columnValue[recordCount] = cell.InnerText.Trim(); }
                            recordCount++;
                        }
                        controlValue++;
                    }
                }
                else if (evaluate == 39) //Does the loop for rows without appraisal record
                {
                    foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr//td[@class='transactionTableBodyText']"))
                    {
                        if (controlValue.Equals(39)) //write to table
                        {
                            columnValue[3] = ""; columnValue[4] = ""; columnValue[5] = ""; columnValue[6] = ""; columnValue[7] = ""; //add blank missing values
                            columnValue[8] = ""; columnValue[9] = ""; columnValue[10] = ""; columnValue[11] = ""; columnValue[12] = ""; columnValue[13] = "";
                            table = ImportDataBuilders.ParcellAppraisalRow(table, columnValue);
                        }
                        else if (controlValue.Equals(1) || controlValue.Equals(4) || controlValue.Equals(33) || controlValue.Equals(34))
                        {
                            if (controlValue.Equals(4))
                            {
                                output = cell.InnerText.Trim();
                                index = output.IndexOf("\r"); output = output.Substring(0, index); //Remove everything after \r
                                columnValue[recordCount] = output;
                            }
                            else { columnValue[recordCount] = cell.InnerText.Trim(); }
                            recordCount++;
                        }
                        controlValue++;
                    }
                }
                else if (evaluate == 40) //Does the loop for rows without appraisal record and comments
                {
                    foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr//td[@class='transactionTableBodyText']"))
                    {
                        if (controlValue.Equals(39)) //write to table
                        {
                            columnValue[3] = ""; columnValue[4] = ""; columnValue[5] = ""; columnValue[6] = ""; columnValue[7] = ""; //add blank missing values
                            columnValue[8] = ""; columnValue[9] = ""; columnValue[10] = ""; columnValue[11] = ""; columnValue[12] = ""; columnValue[13] = "";
                            table = ImportDataBuilders.ParcellAppraisalRow(table, columnValue);
                        }
                        else if (controlValue.Equals(1) || controlValue.Equals(4) || controlValue.Equals(34) || controlValue.Equals(35))
                        {
                            if (controlValue.Equals(4))
                            {
                                output = cell.InnerText.Trim();
                                index = output.IndexOf("\r"); output = output.Substring(0, index); //Remove everything after \r
                                columnValue[recordCount] = output;
                            }
                            else { columnValue[recordCount] = cell.InnerText.Trim(); }
                            recordCount++;
                        }
                        controlValue++;
                    }
                }
            }
            catch (Exception)
            {
                columnValue[0] = ""; columnValue[1] = ""; columnValue[2] = ""; columnValue[3] = ""; columnValue[4] = ""; columnValue[5] = ""; columnValue[6] = ""; columnValue[7] = ""; //add blank missing values
                columnValue[8] = ""; columnValue[9] = ""; columnValue[10] = ""; columnValue[11] = ""; columnValue[12] = ""; columnValue[13] = "";
                table = ImportDataBuilders.ParcellAppraisalRow(table, columnValue);
            }

            return table;
        }

        //Scrapes the Recon Trust Page
        public static DataTable scrapeReconTrust(string page)
        {
            DataTable table = ImportDataBuilders.ReconTrust();
            string output = String.Empty;
            int controlValue = 1;
            int recordCount = 0;
            string[] columnValue = new string[4];
            string regex = "(\\<span.*:)";
            string regex2 = "(\\<.*\\>)";

            HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
            doc.Load(new StringReader(page));
            try
            {
                foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr//td[@class='resultDetails']"))
                {
                    if(controlValue.Equals(3))
                    {
                        columnValue[recordCount] = "RECON"; // adds last value to the array at record position
                        //table = ImportDataBuilders.ReconTrustRow(table, columnValue); //add a row to the datatable
                        //rebuild the ts number and addit
                        //do it again another way
                        controlValue++;
                        recordCount++;
                    }
                    else if (controlValue.Equals(1))
                    {
                        output = Regex.Replace(cell.InnerHtml, regex, "");
                        output = Regex.Replace(output, regex2, "").Trim();

                        int k = output.Length - 1;
                        while (k >= 0)
                        {
                          char digit = output.Substring(k, 1)[0];
                          if (!(digit >= '0' && digit <= '9')) output = output.Remove(k, 1);
                          k--;
                        }
                        output = output.Substring(output.Length - 5, 5);

                        //output = output.Remove(0, 2);
                        //if(output.Length.Equals(9)) //fixes txnumber to match nevada legal 
                        //{
                        //   output = output.Insert(3,"0");
                        //}
                        //else if (output.Length.Equals(8)) //fixes txnumber to match nevada legal 
                        //{
                        //    output = output.Insert(3, "00");
                        //}
                        columnValue[recordCount] = output;
                        controlValue++; recordCount++;
                    }
                    else if (controlValue == 2)
                    {
                        output = Regex.Replace(cell.InnerHtml, regex, "");
                        output = Regex.Replace(output, regex2, "").Trim();
                        columnValue[recordCount] = output;

                        controlValue++;
                        recordCount++;
                    }
                    else if (controlValue == 4)
                    {
                        output = Regex.Replace(cell.InnerHtml, regex, "");
                        output = Regex.Replace(output, regex2, "").Trim();
                        columnValue[recordCount] = output;

                        table = ImportDataBuilders.ReconTrustRow(table, columnValue); //add a row to the datatable
                        controlValue++;
                    }
                    else if (controlValue.Equals(5)) { controlValue = 1; recordCount = 0; } //last value of record
                    else { controlValue++; }//increment the control
                }
            }
            catch (Exception ex)
            {
                columnValue[0] = ""; columnValue[1] = ""; columnValue[2] = ""; //catches blank tables
                table = ImportDataBuilders.ReconTrustRow(table, columnValue);
            }
            return table;
        }

        //Scrapes the NdsCorpPage
        public static DataTable scrapeNdsCorp(string page)
        {
            DataTable table = ImportDataBuilders.NdsCorp();
            string output = String.Empty;
            int controlValue = 1;
            int recordCount = 0;
            string[] columnValue = new string[4];

            HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
            doc.Load(new StringReader(page));
            try
            {
                foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table[@class='layoutTable']//tr[@class='itemRow']/td"))
                {
                    output = cell.InnerText.Trim();

                    if (controlValue == 1)
                    {
                        columnValue[recordCount] = output;
                        controlValue++; recordCount++;
                    }
                    else if (controlValue.Equals(8))//Last usable value
                    {
                        columnValue[recordCount] = "NdsCorp"; // adds last value to the array at record position
                        table = ImportDataBuilders.NdsCorpRow(table, columnValue); //add a row to the datatable
                        controlValue++;
                    }
                    else if (controlValue.Equals(2) || controlValue.Equals(7))
                    {
                        columnValue[recordCount] = output.Trim();
                        controlValue++; recordCount++;
                    }
                    else if (controlValue.Equals(16)) { controlValue = 1; recordCount = 0; } //last value of record
                    else { controlValue++; }//increment the control
                }
            }
            catch (Exception)
            {
                columnValue[0] = ""; columnValue[1] = ""; columnValue[2] = ""; //catches blank tables
                table = ImportDataBuilders.NdsCorpRow(table, columnValue);
            }
            return table;
        }

        //Gets the number of pages form NdsCorp
        public static int getPagesNdsCorp(string page)
        {
             HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
            doc.Load(new StringReader(page));
            string pageString = String.Empty;
            double pages = 0;
            string firstString = "of";
            string lastString = "Total";
            string finalString = String.Empty;
            int pos1;
            int pos2;

            foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//span[@id='lblListCount']"))
            {
                pageString = cell.InnerText;
                pos1 = pageString.IndexOf(firstString) + firstString.Length;
                pos2 = pageString.IndexOf(lastString);
                finalString = pageString.Substring(pos1, pos2 - pos1).Trim();
            }
            pages = Double.Parse(finalString);
            pages = Math.Ceiling(pages / 20);
            return (int)pages;
        }

        /*
        * This Section Does the LpsAsap site scrape for Opening Bid Amounts and Sales Status
       */
        /// <summary>
        /// APN, Sale Status, Opening Bid Amt
        /// </summary>
        /// <param name="page"></param>
        /// <returns></returns>
        public static DataTable scrapeLpsAsap(string page)
        {
            DataTable table = ImportDataBuilders.LpsAsap();

            string apn;
            string saleStatus;
            string amt;

            while (true)
            {
                var idx = page.IndexOf("<b>APN:</b><td align=\"left\">");
                if (idx < 0) break;
                idx = idx + "<b>APN:</b><td align=\"left\">".Length;
                page = page.Remove(0, idx);
                idx = page.IndexOf("</tr>");
                apn = page.Substring(0, idx);


                idx = page.IndexOf("<b>Sale Status:</b><td align=\"left\">");
                if (idx < 0) break;
                idx = idx + "<b>Sale Status:</b><td align=\"left\">".Length;
                page = page.Remove(0, idx);
                idx = page.IndexOf("<tr>");
                saleStatus = page.Substring(0, idx).Replace("<br>", " ").Replace("<br/>", " ");


                idx = page.IndexOf("<b>Opening Bid Amt:</b><td align=\"left\">");
                if (idx < 0) break;
                idx = idx + "<b>Opening Bid Amt:</b><td align=\"left\">".Length;
                page = page.Remove(0, idx);
                idx = page.IndexOf("<tr>");
                amt = page.Substring(0, idx);


                ImportDataBuilders.LpsAsapRow(table, apn, saleStatus, amt);
            }                

            return table;
        }

        //This section does the scrape for the RpsSales Site
        public static DataTable scrapeRpsSales(string page)
        {
            DataTable table = ImportDataBuilders.RpsSaleTable();
            string output = String.Empty;
            string[] columnValue = new string[3];
            int controlValue = 1;
            int recordCount = 0;
            bool burner = true;

            try
            {
                HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
                doc.LoadHtml(page);
                doc.OptionDefaultStreamEncoding = Encoding.UTF8;

                foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table[@id='ctl00_MainContentHolder_ResultsGrid_SalesGrid']//tr//td"))
                {
                    
                    if (burner) //Burns the first 13 values that are not needed
                    {
                        controlValue++;
                        if (controlValue.Equals(13)) { controlValue = 1; burner = false; }
                    }
                    else
                    {
                        if (controlValue.Equals(8))
                        {
                            output = cell.InnerText.Trim(); //get and trim value for opening balance
                            columnValue[recordCount] = output; recordCount++; //Add opening bid
                            columnValue[recordCount] = "RpsSales"; //Add scrape identifier
                            ImportDataBuilders.RpsSaleRow(table, columnValue);
                            controlValue++; recordCount++; //increment counters
                        }
                        else if (controlValue.Equals(1))
                        {
                            columnValue[recordCount] = cell.InnerText.Trim(); //add tsnumber as first value
                            controlValue++; recordCount++; //increment counters
                        }
                        else if (controlValue.Equals(11)) { controlValue = 1; recordCount = 0; } //reesets control values
                        else { controlValue++; }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        //This section does the scrape for the Priority Posting Site
        public static DataTable scrapePriorityPosting(string page)
        {
            DataTable table = ImportDataBuilders.PriorityPosting();
            string[] columnValue = new string[4];
            int controlValue = 1;
            int recordCount = 0;
            bool burner = true;

            try
            {
                HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
                doc.LoadHtml(page);
                doc.OptionDefaultStreamEncoding = Encoding.UTF8;

                string apn = "";
                string bidAmount = "";
                string status = "";

                foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//td/span"))
                {
                    if (controlValue == 12)
                    {
                        ImportDataBuilders.PriorityPostingRow(table,
                            new string[] { status, bidAmount, apn, "PriorPost" });
                        controlValue = 1;
                        continue;
                    }
                    else
                    {
                        string output = cell.InnerText.Trim();

                        if (controlValue == 2) status = output;
                        else if (controlValue == 4) bidAmount = output;
                        else if (controlValue == 7) apn = output;
                    }
                    controlValue++;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }

        //Gets the amount of pages for LpsAsap
        public static int getPagesLpsAsap(string page)
        {
            HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
            doc.Load(new StringReader(page));
            string pageString = String.Empty;
            double pages = 0;

            try
            {
                foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//span[@id='lblResults']//b"))
                {
                    pageString = cell.InnerText;
                }
                pages = Double.Parse(pageString);
                pages = Math.Ceiling(pages / 200);
            }
            catch (Exception)
            {
                pages = 0;
            }
            return (int)pages;
        }

        /*
         * This Section Does the Nevada Legal News Scrape
         * and page count.
        */
        //Scrapes the NevadaLegalNews Website and returns a datatable - DISCONTINUED
        public static DataTable getScrapeNevadaLegalNews(string page)
        {
            string result = string.Empty;
            DataTable table = new DataTable();
            table = ImportDataBuilders.NevadaLegalNews();
            string output = string.Empty;
            string regex = "(\\<.*\\>)"; //used to strip html header

            int recordCount = 0; //Keeps track of current record
            string[] columnValue = new string[13];
            bool is_sold = false;

            HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
            doc.Load(new StringReader(page));

            //Does the loop for even rows
            foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr[@class='even']/td"))
            {
                //clean up string
                output = Regex.Replace(cell.InnerHtml, regex, "");
                output = output.Replace(@"&nbsp;", " ");
                output = output.Trim();

                //Has to check first column for Sold to Bank
                if (recordCount == 0 && output.Equals("Sold to Bank") || output.Equals("Sold to 3rd Party"))
                {
                    is_sold = true; //set tag for sold column
                }

                //checks for a sold to bank property. Has more columns
                if (is_sold)
                {
                    //else add a faux value for the offending row
                    if (recordCount == 12) //last value
                    {
                        columnValue[recordCount] = output; // adds last value to the array at record position
                        //call insert row
                        table = ImportDataBuilders.NevadaLegalNewsRow(table, columnValue); //add a row to the datatable
                        recordCount = 0; // reset count
                        is_sold = false;
                    }
                    else if (recordCount < 12)
                    {
                        //build the array row
                        columnValue[recordCount] = output; // adds value to the array at record position
                        recordCount++;
                    }
                }//if
                else
                {
                    //else add a faux value for the offending row
                    if (recordCount == 12) //last value
                    {
                        columnValue[recordCount] = output; // adds last value to the array at record position
                        //call insert row
                        table = ImportDataBuilders.NevadaLegalNewsRow(table, columnValue); //add a row to the datatable
                        recordCount = 0; // reset count
                    }

                    else if (recordCount < 12 && recordCount != 7)
                    {
                        //build the array row
                        columnValue[recordCount] = output; // adds value to the array at record position
                        recordCount++;
                    }
                    else if (recordCount == 7)
                    {
                        columnValue[recordCount] = ""; // add blank value to fake column
                        recordCount++;
                        columnValue[recordCount] = output;
                        recordCount++;
                    }
                }//else 
            }//End For Each

            recordCount = 0;
            is_sold = false;
            //Does the loop for odd rows
            foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr[@class='odd']/td"))
            {
                //clean up string
                output = Regex.Replace(cell.InnerHtml, regex, "");
                output = output.Replace(@"&nbsp;", " ");
                output = output.Trim();

                //Has to check first column for Sold to Bank
                if (recordCount == 0 && output.Equals("Sold to Bank") || output.Equals("Sold to 3rd Party"))
                {
                    is_sold = true; //set tag for sold column
                }

                //checks for a sold to bank property. Has more columns
                if (is_sold)
                {
                    //else add a faux value for the offending row
                    if (recordCount == 12) //last value
                    {
                        columnValue[recordCount] = output; // adds last value to the array at record position
                        //call insert row
                        table = ImportDataBuilders.NevadaLegalNewsRow(table, columnValue); //add a row to the datatable
                        recordCount = 0; // reset count
                        is_sold = false;
                    }
                    else if (recordCount < 12)
                    {
                        //build the array row
                        columnValue[recordCount] = output; // adds value to the array at record position
                        recordCount++;
                    }
                }//if
                else
                {
                    //else add a faux value for the offending row
                    if (recordCount == 12) //last value
                    {
                        columnValue[recordCount] = output; // adds last value to the array at record position
                        //call insert row
                        table = ImportDataBuilders.NevadaLegalNewsRow(table, columnValue); //add a row to the datatable
                        recordCount = 0; // reset count
                    }

                    else if (recordCount < 12 && recordCount != 7)
                    {
                        //build the array row
                        columnValue[recordCount] = output; // adds value to the array at record position
                        recordCount++;
                    }
                    else if (recordCount == 7)
                    {
                        columnValue[recordCount] = ""; // add blank value to fake column
                        recordCount++;
                        columnValue[recordCount] = output;
                        recordCount++;
                    }
                }//else 
            }//End For Each

            return table;
        }//End scrapeNevadaLegalNews

        //Scrapes the NevadaLegalNews Website and returns a datatable - DISCONTINUED
        public static DataTable getNewScrapeNevadaLegal(string page)
        {
            string result = string.Empty;
            DataTable table = new DataTable();
            table = ImportDataBuilders.NevadaLegalNews();
            string output = string.Empty;
            string regex = "(\\<.*\\>)"; //used to strip html header

            int recordCount = 0; //Keeps track of current record
            string[] columnValue = new string[13];
            bool is_sold = false;
            int burner = 0;

            HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
            doc.Load(new StringReader(page));

            //Does the loop for even rows
            foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr[@class='even']/td"))
            {
                //clean up string
                output = Regex.Replace(cell.InnerHtml, regex, "");
                output = output.Replace(@"&nbsp;", " ");
                output = output.Trim();

                //Has to check first column for Sold to Bank
                if (recordCount == 0 && output.Equals("Sold to Bank") || output.Equals("Sold to 3rd Party"))
                {
                    is_sold = true; //set tag for sold column
                }

                //Has to check first column for Sold to Bank
                if (is_sold)
                {
                    if (burner == 14) //last value
                    {
                        table = ImportDataBuilders.NevadaLegalNewsRow(table, columnValue); //add a row to the datatable
                        recordCount = 0; burner = 0; is_sold = false;
                    }
                    else if (burner.Equals(9) || burner.Equals(10))
                    {
                        burner++;
                    }
                    else if (burner < 14)
                    {
                        columnValue[recordCount] = output;
                        recordCount++; burner++;
                    }
                }
                else
                {
                    if (burner == 13) //last value
                    {
                        table = ImportDataBuilders.NevadaLegalNewsRow(table, columnValue); //add a row to the datatable
                        recordCount = 0; burner = 0;
                    }
                    else if (burner.Equals(8) || burner.Equals(9))
                    {
                        burner++;
                    }
                    else if (burner.Equals(7)) //blank sold price
                    {
                        columnValue[recordCount] = "";
                        burner++; recordCount = 9;
                    }
                    else if (burner < 13)
                    {
                        columnValue[recordCount] = output;
                        recordCount++; burner++;
                    }
                }
            }//End For Loop for the even class

            recordCount = 0; is_sold = false; burner = 0;

            //Does the loop for odd rows
            foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//table//tr[@class='odd']/td"))
            {
                //clean up string
                output = Regex.Replace(cell.InnerHtml, regex, "");
                output = output.Replace(@"&nbsp;", " ");
                output = output.Trim();

                //Has to check first column for Sold to Bank
                if (recordCount == 0 && output.Equals("Sold to Bank") || output.Equals("Sold to 3rd Party"))
                {
                    is_sold = true; //set tag for sold column
                }

                //Has to check first column for Sold to Bank
                if (is_sold)
                {
                    if (burner == 14) //last value
                    {
                        table = ImportDataBuilders.NevadaLegalNewsRow(table, columnValue); //add a row to the datatable
                        recordCount = 0; burner = 0; is_sold = false;
                    }
                    else if (burner.Equals(9) || burner.Equals(10))
                    {
                        burner++;
                    }
                    else if (burner < 14)
                    {
                        columnValue[recordCount] = output;
                        recordCount++; burner++;
                    }
                }
                else
                {
                    if (burner == 13) //last value
                    {
                        table = ImportDataBuilders.NevadaLegalNewsRow(table, columnValue); //add a row to the datatable
                        recordCount = 0; burner = 0;
                    }
                    else if (burner.Equals(8) || burner.Equals(9))
                    {
                        burner++;
                    }
                    else if (burner.Equals(7)) //blank sold price
                    {
                        columnValue[recordCount] = "";
                        burner++; recordCount = 9;
                    }
                    else if (burner < 13)
                    {
                        columnValue[recordCount] = output;
                        recordCount++; burner++;
                    }
                }
            }//End For Loop for the even class
            return table;
        }

        //Get page count for NeveadaLegalNews scrape
        public static int getPagesNevadaLegalNews(string page)
        {
            HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
            doc.Load(new StringReader(page));
            string pageString = String.Empty;
            double pages = 0;

            foreach (HtmlNode cell in doc.DocumentNode.SelectNodes("//input[@name='listingCount']"))
            {
                pageString = cell.GetAttributeValue("value", "");
            }
            pages = Double.Parse(pageString);
            pages = Math.Ceiling(pages / 20);
            return (int)pages;
        }
    }//End Class
}//End Namespace
